<?php
/**
 * insert row to table nilai
 * @param $key
 * @return result
 */
function nilai_select($key=false, $orderby=false, $groupby=false){
	$sql = "select * from nilai";
	if ($key){
		$sql .= " where
				NILAI_ID LIKE '%$key%' OR
				DETACHIEV_ID LIKE '%$key%' OR
				REWARD_ID LIKE '%$key%' OR
				NILAI_POINT	LIKE '%$key%' OR 
				NILAI_MIN LIKE '%$key%' OR
				NILAI_MAX LIKE '%$key%' ";
	}
	if ($groupby) $sql .= " GROUP BY $groupby ";
	if ($orderby) $sql .= " GROUP BY $orderby ";
	$result = mysql_query($sql);
	return !$result? mysql_error() : $result;
}

function nilai_select2($key=false, $where=false, $orderby=false, $groupby=false){
	$sql = "select * from nilai";
	if ($key){
		$sql .= " where
				NILAI_ID LIKE '%$key%' OR
				DETACHIEV_ID LIKE '%$key%' OR
				REWARD_ID LIKE '%$key%' OR
				NILAI_POINT	LIKE '%$key%' OR 
				NILAI_MIN LIKE '%$key%' OR
				NILAI_MAX LIKE '%$key%' ";
	}
	if ($where) $sql .= $key? " AND $where " : " WHERE $where ";
	if ($groupby) $sql .= " GROUP BY $groupby ";
	if ($orderby) $sql .= " ORDER BY $orderby ";
	$result = mysql_query($sql);
	return !$result? mysql_error() : $result;
}

function nilai_generate_id(){
	//	$sql = "SELECT max(substring(`REWARD_ID`,2)) as max FROM `rewarddanpunishment` WHERE 1";
	//	$result = mysql_query($sql);
	//	$result = mysql_fetch_assoc($result);
	//	return 'R'.($result['max']+1);

	return false;
}

/**
 * load row from table reward&punishment
 * @param $id
 * @return result
 */
function nilai_load($id){
	$sql = "select * from nilai
			where NILAI_ID = '$id'";
	return mysql_query($sql);
}

/**
 * check wheter the specific id is exist on the table reward&punishment
 * @param $id specific id
 * @return boolean value (true:exist, false:otherwise)
 */
function nilai_isExistID($id){
	$sql = "select * from nilai
			where NILAI_ID = '$id'";

	$result = mysql_query($sql);
	$result = mysql_fetch_assoc($result);
	if ($res['NILAI_ID'] === $id) return true;
	return false;
}


/**
 * @param unknown_type $NILAI_ID
 * @param unknown_type $NILAI_POINT
 * @param unknown_type $DETACHIEV_ID
 * @param unknown_type $REWARD_ID
 * @return resource
 */
function nilai_add($NILAI_ID, $DETACHIEV_ID, $REWARD_ID, $NILAI_POINT, $NILAI_MIN, $NILAI_MAX){
	$sql = "insert into nilai ".
			"values ('$NILAI_ID', '$DETACHIEV_ID', '$REWARD_ID', '$NILAI_POINT', '$NILAI_MIN', '$NILAI_MAX')";
	return mysql_query($sql);
}

/**
 * @param unknown_type $NILAI_ID
 * @param unknown_type $NILAI_NAMA
 * @param unknown_type $DETACHIEV_ID
 * @param unknown_type $REWARD_ID
 * @return resource
 */
function nilai_update($NILAI_ID, $DETACHIEV_ID, $REWARD_ID, $NILAI_POINT, $NILAI_MIN, $NILAI_MAX){
	$sql = "update nilai ".
			"set DETACHIEV_ID = '$DETACHIEV_ID', 
				REWARD_ID = '$REWARD_ID',
				NILAI_POINT = '$NILAI_POINT',
				NILAI_MIN = '$NILAI_MIN',
				NILAI_MAX = '$NILAI_MAX'
			where NILAI_ID ='$NILAI_ID'";
	return mysql_query($sql);
}

/**
 * delete a row in reward & punishment table
 * @param $gol_id
 * @return boolean
 */
function nilai_delete($id){
	$sql = "delete from nilai ".
			"where NILAI_ID='$id'";
	return mysql_query($sql);
}

/**
 * return point dari nilai dengan range spesifik
 * @return (int) point nilai pada range spesifik
 */
function nilai_getPoint($detachiev_id, $nilai){
	/**
	 * aturan pencarian:
	 * 1. cari POINT dari NILAI dengan range yang sesuai
	 * 2. jika tidak ditemukan, gunakan range satu level sebelumnya
	 */
	$prev = NULL;
	$current = NULL;
	$sql = "SELECT * FROM NILAI WHERE DETACHIEV_ID='$detachiev_id' ORDER BY NILAI_MIN";
	$sql = mysql_query($sql);
	while ($row = mysql_fetch_assoc($sql)){
		
		//cek range
		$current = ($row['NILAI_MIN']<=$nilai && $nilai<=$row['NILAI_MAX'])? $row : NULL;
		if ($current) break;

		//sampe sini asumsikan tidak ditemukan range yang sesuai.
		//simpan referensi pada $prev, nanti digunakan
		$prev = $row;
	}
	
	$current = $current? $current : $prev;
	return $current['NILAI_POINT'];
}